package cn.jit.ssm.demo;

import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.Date;


import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFCreationHelper;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellRangeAddress;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;


/**
 * 导出Excel的Demo
 *
 * @author: Cecurio
 * @create: 2017-12-15 9:17
 **/
public class CreateExcelSheet {
    public static void test1() throws Exception {

        SXSSFWorkbook wb = new SXSSFWorkbook(100); // keep 100 rows in memory, exceeding rows will be flushed to disk
        Sheet sh = wb.createSheet("Sample sheet");

        // Aqua background
        CellStyle style = wb.createCellStyle();
        style.setFillForegroundColor(IndexedColors.ORANGE.getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);

        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        style.setAlignment(CellStyle.ALIGN_CENTER);


        for (int rownum = 0; rownum < 1000; rownum++) {
            Row row = sh.createRow(rownum);
            for (int cellnum = 0; cellnum < 10; cellnum++) {

                Cell cell = row.createCell(cellnum);
                String address = new CellReference(cell).formatAsString();
                cell.setCellValue(address);

                if (rownum == 0) {
                    cell.setCellStyle(style);
                }
            }

        }

        sh.addMergedRegion(new CellRangeAddress(
            0, //first row (0-based)
            0, //last row  (0-based)
            0, //first column (0-based)
            5  //last column  (0-based)
        ));


        // Rows with rownum < 900 are flushed and not accessible
//        for(int rownum = 0; rownum < 900; rownum++){
//            Assert.assertNull(sh.getRow(rownum));
//        }
//
//        // their last 100 rows are still in memory
//        for(int rownum = 900; rownum < 1000; rownum++){
//            Assert.assertNotNull(sh.getRow(rownum));
//        }

        File f = new File("D:\\Tmp\\excel\\Example2.xlsx");


        if (!f.exists()) {
            //If directories are not available then create it
            File parent_directory = f.getParentFile();
            if (null != parent_directory) {
                parent_directory.mkdirs();
            }

            f.createNewFile();
        }

        FileOutputStream out = new FileOutputStream(f, false);
        wb.write(out);
        out.close();

        // dispose of temporary files backing this workbook on disk
        wb.dispose();
        System.out.println("File is created");
    }

    public static void exportXlsExcel_1() {

        try {
            // 创建一个Excel文件
            HSSFWorkbook hssfWorkbook = new HSSFWorkbook();

            // 新建一个工作表,默认名字为Sheet0
            HSSFSheet hssfSheet = hssfWorkbook.createSheet();
            // 继续新建一个工作表,默认名字为Sheet1
            HSSFSheet hssfSheet1 = hssfWorkbook.createSheet();

            // 打开是显示的是第一张sheet
            hssfWorkbook.setActiveSheet(0);

            String filePath = "D:\\Tmp\\excel\\HSSFSheet001.xls";
            File file = new File(filePath);
            OutputStream outputStream = new FileOutputStream(file);
            hssfWorkbook.write(outputStream);
            System.out.println("导出成功！");
        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("导出失败！");
        }
    }

    public static void exportXlsExcel_2() {

        try {
            // 创建一个Excel文件
            HSSFWorkbook hssfWorkbook = new HSSFWorkbook();

            // 新建一个工作表,默认名字为Sheet0
            HSSFSheet hssfSheet = hssfWorkbook.createSheet();
            HSSFSheet hssfSheet1 = hssfWorkbook.createSheet();
            HSSFSheet hssfSheet2 = hssfWorkbook.createSheet();

            // 打开是显示的是第一张sheet,如果不设置，默认打开时停在第一张表
            hssfWorkbook.setActiveSheet(0);

            String filePath = "D:\\Tmp\\excel\\HSSFSheet002.xls";
            File file = new File(filePath);
            OutputStream outputStream = new FileOutputStream(file);
            hssfWorkbook.write(outputStream);
            System.out.println("导出成功！");
        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("导出失败！");
        }
    }

    public static void exportXlsExcel_3() {

        try {
            // 创建一个Excel文件
            HSSFWorkbook hssfWorkbook = new HSSFWorkbook();

            // 新建一个工作表,默认名字为Sheet0
            HSSFSheet hssfSheet = hssfWorkbook.createSheet();

            HSSFRow firstRow = hssfSheet.createRow(0);
            firstRow.createCell(0).setCellValue("id");
            firstRow.createCell(1).setCellValue("name");
            firstRow.createCell(2).setCellValue("age");

            HSSFRow tempRow = null;

            for (int i = 1; i <= 10; i++) {
                tempRow = hssfSheet.createRow(i);
                tempRow.createCell(0).setCellValue("id" + i);
                tempRow.createCell(1).setCellValue("name" + i);
                tempRow.createCell(2).setCellValue("age" + i);
            }

            String filePath = "D:\\Tmp\\excel\\HSSFSheet003.xls";
            File file = new File(filePath);
            OutputStream outputStream = new FileOutputStream(file);
            hssfWorkbook.write(outputStream);
            System.out.println("导出成功！");
        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("导出失败！");
        }
    }

    public static void createExcel() throws IOException {
        String filePath = "D:\\Tmp\\excel\\HSSFSheet004.xls";
        File file = new File(filePath);
        OutputStream outputStream = new FileOutputStream(file);
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("Sheet1");
        HSSFRow row = sheet.createRow(0);
        row.createCell(0).setCellValue("id");
        row.createCell(1).setCellValue("订单号");
        row.createCell(2).setCellValue("下单时间");
        row.createCell(3).setCellValue("个数");
        row.createCell(4).setCellValue("单价");
        row.createCell(5).setCellValue("订单金额");
        row.setHeightInPoints(30); // 设置行的高度

        HSSFRow row1 = sheet.createRow(1);
        row1.createCell(0).setCellValue("1");
        row1.createCell(1).setCellValue("NO00001");

        // 日期格式化
        HSSFCellStyle cellStyle2 = workbook.createCellStyle();
        HSSFCreationHelper creationHelper = workbook.getCreationHelper();
        cellStyle2.setDataFormat(creationHelper.createDataFormat().getFormat("yyyy-MM-dd HH:mm:ss"));
        sheet.setColumnWidth(2, 20 * 256); // 设置列的宽度

        HSSFCell cell2 = row1.createCell(2);
        cell2.setCellStyle(cellStyle2);
        cell2.setCellValue(new Date());

        row1.createCell(3).setCellValue(2);


        // 保留两位小数
        HSSFCellStyle cellStyle3 = workbook.createCellStyle();
        cellStyle3.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00"));
        HSSFCell cell4 = row1.createCell(4);
        cell4.setCellStyle(cellStyle3);
        cell4.setCellValue(29.5);


        // 货币格式化
        HSSFCellStyle cellStyle4 = workbook.createCellStyle();
        HSSFFont font = workbook.createFont();
        font.setFontName("华文行楷");
        font.setFontHeightInPoints((short) 15);
        font.setColor(HSSFColor.RED.index);
        cellStyle4.setFont(font);

        HSSFCell cell5 = row1.createCell(5);
        cell5.setCellFormula("D2*E2");  // 设置计算公式
        cell5.setCellStyle(cellStyle4);

        // 获取计算公式的值
        HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(workbook);
        cell5 = e.evaluateInCell(cell5);
        System.out.println(cell5.getNumericCellValue());

        workbook.setActiveSheet(0);
        workbook.write(outputStream);
        outputStream.close();
    }

    public static void readXlsx() {
        try {
            File file = new File("D:\\Tmp\\excel\\user.xlsx");
            // may throw java.io.FileNotFoundException
            InputStream inputStream = new FileInputStream(file);

            XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
            XSSFSheet sheet = workbook.getSheetAt(0);

            int lastRowNum = sheet.getLastRowNum();
            System.out.println("有多少行：" + (lastRowNum + 1));
            for (int i = 0; i <= lastRowNum; i++) {
                XSSFRow row = sheet.getRow(i);
                int id = (int) row.getCell(0).getNumericCellValue();
                String name = row.getCell(1).getStringCellValue();
                int age = (int) row.getCell(2).getNumericCellValue();
                System.out.println(id + "-" + name + "-" + age);
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) throws IOException {
        readXlsx();
    }
}
